/*
 * Copyright 2015, Yahoo Inc.
 * Copyrights licensed under the Apache License.
 * See the accompanying LICENSE file for terms.
 */
package com.yahoo.dba.perf.myperf.metrics;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.yahoo.dba.perf.myperf.common.Metric;
import com.yahoo.dba.perf.myperf.common.MetricDataType;
import com.yahoo.dba.perf.myperf.common.MetricsGroup;



/**
 * persistence store for metrics
 * @author xrao
 *
 */
public class DerbyMetricsDb extends MetricsDbBase implements java.io.Serializable, Runnable
{
  private static final long serialVersionUID = 1L;
  private static Logger logger = Logger.getLogger(DerbyMetricsDb.class.getName());  
  

  public DerbyMetricsDb()
  {
    this.setPassword("metricsdb");
  }
  public String getDbType() 
  {
    return "derby";
  }
  public String getConnectionString() 
  {
    return "jdbc:derby:metricsdb";
  }

  
  protected String buildMetricCodeDDL()
  {
	    StringBuilder sb = new StringBuilder();
	    sb.append("CREATE TABLE METRIC_CODE (")
	    .append("CODE_ID INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), NAME VARCHAR(256))");
	    return sb.toString();
	  
  }
  
  @Override
  protected String buildMetricCodeIndexDDL() {
	return "create unique index UK_COCE on METRIC_CODE (NAME)";
  }

  protected String buildAlertDDL()
  {
	    StringBuilder sb = new StringBuilder();
	    sb.append("CREATE TABLE ALERT (")
	    .append("DBID INT, TS BIGINT, END_TS BIGINT DEFAULT 0, ALERT_TYPE VARCHAR(30), ALERT_REASON VARCHAR(128), PRIMARY KEY(DBID, TS))");
	    return sb.toString();
	  
  }
  
  @Override
  protected String buildAlertIndexDDL() {
	return "create index IDX_ALERT_TS on ALERT(TS)";
  }

  protected String[] buildGenericMetricDDL()
  {
	    //StringBuilder sb = new StringBuilder();
	    //sb.append("CREATE TABLE METRIC_GENERIC (")
	    //.append("DBID INT, METRIC_ID INT, TS BIGINT, VALUE DECIMAL(22,7)");
	    //sb.append(", PRIMARY KEY(DBID, METRIC_ID, TS))");
	    //return sb.toString();
	  
	    return new String[]{
	    		"CREATE TABLE METRIC_GENERIC (DBID INT, METRIC_ID INT, SNAP_ID INT, TS BIGINT, VALUE DECIMAL(22,7), PRIMARY KEY(DBID, METRIC_ID, SNAP_ID))",
	    		"create index idx_metrics_dbid_ts on METRIC_GENERIC(DBID, SNAP_ID)",
	    		"create index idx_metrics_ts on METRIC_GENERIC(SNAP_ID)"
	    };
  }
  
  
  protected Connection createConnection(boolean autocommit)
  {
    Connection conn = null;
	try
	{	
	  conn = DriverManager.getConnection(this.getConnectionString()+";create=true",this.getSchemaName(), this.getPassword());	 
	  return conn;
	}catch(Exception ex)
    {
		logger.log(Level.SEVERE,"Exception", ex);
	}

    return conn;
  }
  
  @Override
  protected String buildDDL(MetricsGroup mg)
  {
    StringBuilder sb = new StringBuilder();
    String tblName = mg.getSinkTableName();
    sb.append("CREATE TABLE ");
    sb.append(tblName);
    sb.append(" (")
    .append("DBID INT, SNAP_ID INT");
    if(mg.getKeyColumn() != null && !mg.getKeyColumn().isEmpty())
    {
    	//add additional column to store key
    	sb.append(", KEY_COLUMN VARCHAR(255)");
    }
    sb.append(", TS BIGINT, SQL_TIME INT");
    for(Metric m: mg.getMetrics())
    {
      sb.append(",");
      sb.append(m.getName().toUpperCase()).append(" ");
      if(m.getDataType()==MetricDataType.BYTE)
        sb.append("SMALLINT");
      else if(m.getDataType()==MetricDataType.SHORT)
          sb.append("SMALLINT");
      else if(m.getDataType()==MetricDataType.INT)
          sb.append("INT");
      else if(m.getDataType()==MetricDataType.LONG)
          sb.append("BIGINT");
      else if(m.getDataType()==MetricDataType.FLOAT)
          sb.append("DECIMAL(22,7)");
      else if(m.getDataType()==MetricDataType.DOUBLE)
          sb.append("DECIMAL(22,7)");
    }
    if(mg.getKeyColumn() != null && !mg.getKeyColumn().isEmpty())
    	sb.append(", PRIMARY KEY(DBID, SNAP_ID, KEY_COLUMN))");
    else
    	sb.append(", PRIMARY KEY(DBID, SNAP_ID))");
    return sb.toString();
  }

  @Override
  protected String[] buildHostDDL() {
	  return new String[]
			  {
			  	"create table DBINFOS(DBID INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), DBTYPE VARCHAR(30), DBGROUPNAME VARCHAR(30),INSTANCE SMALLINT, HOSTNAME VARCHAR(100),PORT SMALLINT, DATABASE_NAME VARCHAR(60),USE_SSHTUNNEL SMALLINT, LOCAL_HOSTNAME VARCHAR(100), LOCAL_PORT SMALLINT, CREATED TIMESTAMP DEFAULT CURRENT TIMESTAMP, CONNECTION_VERIFIED SMALLINT DEFAULT 0,VIRTUAL_HOST SMALLINT DEFAULT 0,SNMP_ENABLED SMALLINT DEFAULT 1, METRICS_ENABLED SMALLINT DEFAULT 1, ALERT_ENABLED SMALLINT DEFAULT 1, OWNER VARCHAR(30))",
			  	"create unique index UK_DBINFOS on DBINFOS (DBGROUPNAME, HOSTNAME)"
			  };
  }
@Override
protected String[] buildAlertSettingDDL() {
	return new String[]
	{
			"create table " + MetricsDbBase.ALERTSETTING_TABLENAME +"(DBGROUPNAME VARCHAR(30), HOSTNAME VARCHAR(100), ALERT_TYPE VARCHAR(30), THRESHOLD DECIMAL(22,7), RESERVED VARCHAR(255), CREATED TIMESTAMP DEFAULT CURRENT TIMESTAMP, PRIMARY KEY(DBGROUPNAME, HOSTNAME, ALERT_TYPE))",
	};
}
  @Override
  protected String[] buildSnapshotDDL() {
	return new String[]{
			"create table SNAPSHOTS(SNAP_ID INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), START_TS BIGINT, END_TS BIGINT, FAILED_HOSTS INT, SUCCEED_HOSTS INT, CREATED TIMESTAMP DEFAULT CURRENT TIMESTAMP)",
    		"create index IDX_SNAPSHOTS_TS on SNAPSHOTS(START_TS)"	
	};
  }
  @Override
  protected String[] buildAlertSubScriptionDDL() {
    return new String[]{"CREATE TABLE ALERT_SUBSCRIPT ("
	         + "ID INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), DBGROUP VARCHAR(30) NOT NULL, HOSTNAME VARCHAR(100), ALERT_NAME VARCHAR(30) NOT NULL, PARAMS VARCHAR(512))",
	         "create unique index UK_ALERT_SUBSCRIPT on ALERT_SUBSCRIPT (DBGROUP, HOSTNAME, ALERT_NAME)",
	         "create index IDX_ALERT_SUBSCRIPT1 on ALERT_SUBSCRIPT (ALERT_NAME)",
    };
  }
  
  @Override
  protected String[] buildMetricsSubscrptionDDL() {
	    return new String[]{"CREATE TABLE METRICS_SUBSCRIPT ("
	   	     + "ID INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), DBGROUP VARCHAR(30) NOT NULL, HOSTNAME VARCHAR(100), MGROUP VARCHAR(30) NOT NULL,  MSUBGROUP VARCHAR(30))",
	   	     "create unique index UK_METRICS_SUBSCRIPT on METRICS_SUBSCRIPT (DBGROUP, HOSTNAME, MGROUP, MSUBGROUP)",
	   	     "create unique index IDX_METRICS_SUBSCRIPT1 on METRICS_SUBSCRIPT (MGROUP, MSUBGROUP)"	   	     
	    };
  }
}
  
